package controllers;

import play.*;
import play.db.DB;
import play.mvc.*;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.google.gson.Gson;

import lib.UtilsInput;
import models.*;

public class Reporte_8 extends Controller {
	
	public static void Reporte_8() {
		if(Security.isAuthenticated() && Security.havePermission())
			render();
		else
			Application.login();
	}
	
	public static void getReporte8(String anio,String mes, String depto, String servicio){
		String query = "SELECT sol_servicio AS servicio, sol_depto AS departamento, COUNT(*) AS cantidad " +
						"FROM sigpc_solicitud " +
						"GROUP BY sol_servicio, sol_depto " +
						"ORDER BY sol_depto";
		String str = null;
		Gson gson = new Gson();
		Connection conn = DB.getConnection();
	
		/*
		 * Lista de objetos 
		 */
		List<Reporte8> listReporte = new ArrayList<Reporte8>();
		try {
			if (mes != null &&  anio != null && depto != null &&  servicio != null)
			{
				if (!mes.equalsIgnoreCase("0") &&  !anio.equalsIgnoreCase("0") )
					query = "SELECT sol_servicio AS servicio, sol_depto AS departamento, COUNT(*) AS cantidad " +
							"FROM sigpc_solicitud " +
							"WHERE extract(year from sol_fecha) = "+ anio +" and extract(month from sol_fecha) = "+ mes +" "+
							"AND sol_depto LIKE ('"+depto+"') " +
							"AND sol_servicio LIKE ('"+servicio+"') " +
							"GROUP BY sol_servicio, sol_depto " +
							"ORDER BY sol_depto";
			}

		ResultSet rs = conn.createStatement().executeQuery(query);
		
		 while (rs.next()) {
			 Reporte8 reporte = new Reporte8(rs.getRow(), rs.getString("servicio"), rs.getString("departamento"),rs.getInt("cantidad"));
			 listReporte.add(reporte);
		 }
		 
		 if(listReporte.size()!=0){
			 str = "{\"success\":true ,\"records\":" + listReporte.size()
						+ ", \"total\":1 , \"page\":1, \"rows\":" + gson.toJson(listReporte)
						+ "}";				
			 renderJSON(str);			 
		 }
		 else{
			 str = "{\"success\":false,\"records\":" + listReporte.size()
						+ ", \"total\":0 , \"page\":0}";
				
				 renderJSON(str);
		 }		 
     	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//JASPER REPORT XLS
		public static void  download_report(String anio, String mes1, String mes2, String depto, String servicio, String formato) {
		    Map reportParams = new HashMap();
		    
		   if (formato != null)
		   {
			   reportParams.put("mes1", mes1);
			   reportParams.put("mes2", mes2);
			   reportParams.put("anio", anio);
			   reportParams.put("depto",  depto);
			   reportParams.put("servicio",  servicio);
		    
		  if (formato.equalsIgnoreCase("html"))
			renderHtml( UtilsInput.toString((ByteArrayInputStream) lib.BaseJasperReport.generateReport("reporte_8", reportParams,formato)));
			
		  else
			  renderBinary(lib.BaseJasperReport.generateReport("reporte_8", reportParams,formato), "reporte_8."+ formato);
		   
		   }
		  }

}